package com.yuda.tools.downloadanswer.mapper;

import com.yuda.tools.downloadanswer.dto.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.Date;
import java.util.List;

public interface DownLoadAnswerMapper {

    List<StudentOssDto> listStudentsForDownLoad(@Param("testSession") int testSession, @Param("subjectIndex") int subjectIndex);

    void batchSaveAnswer(@Param("list") List<StudentAnswer> list);

    List<PaperSubject> downLodAnswerList();

    List<DownLoadProcess> findDownLoadStatus();

    @Update("update sparta_exam_subject_student set download_type = 1 where  id = #{sessId}")
    void updateStatus(Integer sessId);

    //判断有没有去重表
    @Select("SELECT count(1) FROM Information_schema.`TABLES`  WHERE table_Name = 'mq_answer_qc' AND TABLE_SCHEMA = #{currentSchema}")
    int determineCanBeExecuted(String currentSchema);

    //建表操作
    @Update("create table mq_answer_qc like mq_answer")
    void doCreateTableMqQc();

    //去重表有没有记录
    @Select("select count(1) from mq_answer_qc")
    int haveAnyRecord();

    //去重操作
    @Update("insert into mq_answer_qc (student_id,subject_id,subject_relation_id,ques_id,paper_id,answer,answer_json,md5,type,create_time,ques_type) " +
            " select student_id,subject_id,subject_relation_id,ques_id,paper_id,answer,answer_json,md5,type,create_time,ques_type from( " +
            " select row_number() over (partition by student_id,subject_id,ques_id order by create_time desc) as row_num, " +
            " student_id,subject_id,subject_relation_id,ques_id,paper_id,answer,answer_json,md5,type,create_time,ques_type from mq_answer where type = 2 " +
            "   )t where row_num=1")
    void removeMqDuplicates();


    @Select("select c.`name` as provinceName,b.exam_name kaodian,b.exam_room_name jifang,b.`name` as studentName,b.zkz_num as zkzNum,aa.num,count(1) mqNum from (" +
            " SELECT student_id,subject_id,ques_id from tsinghua_formal_answer where answer = -1 GROUP BY student_id,subject_id,ques_id" +
            ")  a  " +
            " LEFT JOIN sparta_exam_student b on a.student_id = b.student_id " +
            " LEFT JOIN sparta_province c on c.id = b.province_id " +
            " LEFT JOIN mq_answer_qc d on d.student_id = a.student_id and d.subject_id = a.subject_id and d.ques_id= a.ques_id " +
            " LEFT JOIN (SELECT student_id,count(1) as num from tsinghua_formal_answer where answer = -1 GROUP BY student_id) aa on a.student_id = aa.student_id" +
            " where b.e_status =0 and d.answer!=-1 GROUP BY d.student_id ")
    List<TroubleMaker> listUnAssemblyData();

    @Update("insert into tsinghua_formal_answer (student_id,zkz_num,subject_id,subject_relation_id,ques_id,paper_id,answer,answer_json,md5,type,create_time,answer_source)" +
            " select student_id,zkz_num,subject_id,subject_relation_id,ques_id,paper_id,answer,answer_json,md5,type,create_time,1  from tsinghua_formal_answer_marked")
    void unionFormalAnswer();

    //删除异常表合并过来的数据
    @Update("delete from tsinghua_formal_answer where answer_source =1")
    int cleanUpOldFormalAnswerData();

    @Update("update tsinghua_formal_answer a, mq_answer_qc b set a.answer = b.answer " +
            " where a.student_id = b.student_id and a.subject_id = b.subject_id and a.ques_id = b.ques_id " +
            "and a.answer =-1 and b.answer !=-1")
    int updateAnswerFromMq();

    @Select("select pc_no from sparta_exam_student where student_id = #{studentId} and e_status =0")
    int getPcNoByStudentId(String studentId);

    @Update("truncate table mq_answer_qc")
    int truncateMqQc();

    @Select("select paper_id from sparta_paper_subject_relation where test_session = #{testSession} GROUP BY paper_id")
    int getPaperIdByTestSession(Integer testSession);

    //    @Select("select \n" +
//            "        es.student_id,es.log_area,es.pc_no,'1' as pc_status  from\n" +
//            "        (select student_id from sparta_exam_subject_student where paper_id = #{paperId} and stu_start_time >0 GROUP BY student_id) ss\n" +
//            "        LEFT JOIN sparta_exam_student es on ss.student_id = es.student_id\n" +
//            "        where es.student_id !='' and es.student_id is not null\n" +
//            "        and es.log_db_type = 1 and es.log_download_type = 1  and es.student_id !='000000' and es.name !='test' and\n" +
//            "        es.computerroom_id !=1280 GROUP BY es.student_id  HAVING count(1) = 1\n" +
//            "        UNION ALL\n" +
//            "        select student_id,log_area,old_pc_no,'2' as pc_status from sparta_change_student where paper_id = #{paperId} and log_db_type =1 and log_download_type = 0 GROUP BY student_id,paper_id ")
    @Select("select id,log_area as realArea,pc_no as pcNo,'1' as pcStatus " +
            "from log_student_paper where log_db_type = 1 and log_download_type = 0 "+
            " UNION " +
            "select student_id,log_area,old_pc_no,'2' as pc_status from sparta_change_student " +
            " where paper_id = #{paperId} and log_db_type =1 and " +
            "log_download_type = 0 GROUP BY student_id,old_pc_no")
    List<StudentLog> listStudentsLogForDownLoad(int paperId);

    void batchSaveLog(@Param("list") List<LogContext> list);

    @Update("update log_student_paper set log_download_type = 1 where id = #{id}")
    void updateExamStudentStatusForLog(Integer id);

    @Update("update sparta_change_student set log_download_type =1 where student_id = #{studentId} and old_pc_no = #{pcNo}")
    void updateChangeTableStatusForLog(@Param("studentId") String studentId, @Param("pcNo") Integer pcNo);

    @Update("delete from tsinghua_formal_log where paper_id = #{paperId}")
    void deleteByPaperId(int paperId);

    @Select("select count(1) from tsinghua_formal_log where paper_id = #{paperId}")
    int checkCanBeExecute(int paperId);

    @Select("select subject_id from sparta_paper_subject_relation where test_session = #{testSession} and subject_index = #{subjectIndex}")
    List<Integer> getSubjectId(@Param("testSession") int testSession, @Param("subjectIndex") int subjectIndex);

    @Select("select num from ques_count where subject_id = #{subjectId}")
    int numSubjectCount(int subjectId);

    @Select("select count(1) from (\n" +
            " SELECT student_id from tsinghua_formal_answer where subject_id = #{subjectId} GROUP BY student_id ,subject_id  HAVING count(1) != #{subjectCount} ) t")
    Integer authenticationData(@Param("subjectId") int subjectId, @Param("subjectCount") int subjectCount);

    @Update("insert into stu_download_error (subject_id,num,create_time) values (#{subjectId},#{subjectCount},#{createTime})")
    void saveError(@Param("subjectId") int subjectId, @Param("subjectCount") int subjectCount, @Param("createTime") Date createTime);

    @Select("SELECT count(id) from tsinghua_formal_answer where subject_id = #{subjectId}")
    int hasDataFormalAnswer(Integer subjectId);

    @Update("update sparta_paper_subject_relation set download_success = 1 where subject_id = #{subjectId}")
    void updateDownLoadSuccess(Integer subjectId);

    @Select("SELECT if(count(id)= (select count(id) from sparta_paper_subject_relation),'y','n') success_status  from sparta_paper_subject_relation where download_success = 1")
    String checkCanBeUpdateRoadMap(Integer subjectId);

    @Update("update road_map set download_answer = 1")
    void updateDownLoadRoadMap();

    @Update("update road_map set merge_answer = 1")
    void updateMergeRoadMap();

    @Update("update tsinghua_formal_answer a, (select student_id,zkz_num from sparta_exam_subject_student GROUP BY student_id) b set a.zkz_num = b.zkz_num where a.student_id = b.student_id and a.zkz_num is null")
    void updateZkz();
}
